//
//  YWInspectionMissionDBManager.m
//  maintain
//
//  Created by Kyo on 16/8/31.
//  Copyright © 2016年 ewin. All rights reserved.
//

#import "YWInspectionMissionDBManager.h"
#import "YWDBTool.h"
#import "YWCommonTool.h"
#import "YWLoginTool.h"
#import "YWLoginUser.h"

#import "YWKeepWatchMission.h"
#import "YWInspectionMissionManager.h"
#import "YWLocation.h"
#import "YWEquipment.h"
#import "YWEquipmentFieldRecord.h"
#import "YWReply.h"
#import "YWReplyDBManager.h"

@implementation YWInspectionMissionDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

- (void)createAllInspectionTables{
    //版本2.2.2,版本2.2.3
    [self createInspectionMissionTable];
    [self createInspectionMissionAndExecutorRelTable];
    [self createInspectionLineTable];
    [self createInspectionLineAndEquipmentRelTable];
    [self createInspectionlineRecordTable];
    [self createInspectionEquipmentRecordTable];
    
    //3.0.0
    [self createInspectionWeekRelTable];
    //[self addColumnInInspectionMissionTable];
    
    //3.1.0
    //[self addColumnInInspectionLineTable];
    [self createInspectionLineAndLocationRelTable];
    //[self addColumnInKeepWatchAndInspectionMissionTable];

}

- (void)dropAndReCreateInspectionTables{
    //3.0.0(任务编号)
    [self dropAndReCreateInspectionMissionTable];
    //3.1.0(线路类型)
    [self dropAndReCreateInspectionLineTable];
    //3.2.1
    [self dropAndReCreateInspectionlineRecordTable];
    [self dropAndReCreateInspectionEquipmentRecordTable];
}

#pragma mark - 设备巡查任务
NSString *const KSqlCreateInspectionMissionTable = @"CREATE TABLE IF NOT EXISTS 'inspectionMission' (inspectionMissionId integer,inspectionLineId integer,inspectionMissionName text,createTime timestamp,status smallint,creatorId integer,startDate timestamp,endDate timestamp,startTime varchar(10),endTime varchar(10),missionSequence text,buildingId varchar,observerIds varchar);";

- (BOOL)createInspectionMissionTable
{
    return [[self fmdb] executeUpdate:KSqlCreateInspectionMissionTable];
}

- (void)dropAndReCreateInspectionMissionTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists inspectionMission;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreateInspectionMissionTable];
        }
    }];
}
#pragma mark - 设备巡查任务与执行人关系表
- (BOOL)createInspectionMissionAndExecutorRelTable
{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'inspectionMission_executor_rel' (inspectionMissionId integer,uniqueId integer);";
    return [[self fmdb] executeUpdate:sql];
}
#pragma mark - 设备巡查路线
NSString *const KSqlCreateInspectionLineTable = @"CREATE TABLE IF NOT EXISTS 'inspectionLine' (inspectionLineId integer,inspectionLineName text,lineType smallint,intervalTime integer,creatorId integer,createTime timestamp,updateTime timestamp,status smallint,note text);";

- (BOOL)createInspectionLineTable
{
    return [[self fmdb] executeUpdate:KSqlCreateInspectionLineTable];
}

- (void)dropAndReCreateInspectionLineTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists inspectionLine;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreateInspectionLineTable];
        }
    }];
}
#pragma mark - 设备巡查任务执行星期表
- (BOOL)createInspectionWeekRelTable
{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'inspectionLine_week_rel' (inspectionLineId integer NOT NULL,week integer);";
    return [[self fmdb] executeUpdate:sql];
}
#pragma mark - 设备巡查路线与设备关系表
- (BOOL)createInspectionLineAndEquipmentRelTable
{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'inspectionLine_equipment_rel' (inspectionLineId integer,equipmentId varchar(24));";
    return [[self fmdb] executeUpdate:sql];
}
#pragma mark - 设备巡查路线与位置关系表
- (BOOL)createInspectionLineAndLocationRelTable
{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'inspectionLine_location_rel' (inspectionLineId integer,sort integer,locationId integer,note text);";
    return [[self fmdb] executeUpdate:sql];
}
#pragma mark - 巡查线路记录表
NSString *const KSqlCreateInspectionlineRecordTable = @"CREATE TABLE IF NOT EXISTS 'inspectionline_record' (inspectionLineId integer, uniqueTag varchar,startTime timestamp,endTime timestamp,status smallint,count integer,result integer,executorIds text,inspectionMissionId integer,isRead boolean DEFAULT 0,recordType smallint,recordSequence varchar);";

- (BOOL)createInspectionlineRecordTable
{
    return [[self fmdb] executeUpdate:KSqlCreateInspectionlineRecordTable];
}

- (void)dropAndReCreateInspectionlineRecordTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists inspectionline_record;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreateInspectionlineRecordTable];
        }
    }];
}
#pragma mark - 巡查点记录表
NSString *const KSqlCreateInspectionEquipmentRecordTable = @"CREATE TABLE IF NOT EXISTS 'inspectionEquipment_record' (inspectionEquipmentRecordId varchar,uniqueTag varchar,status smallint,equipmentId varchar(24), finishTime timestamp ,recordNote varchar(200),uploadStatus integer,executorId integer,inspectionLineId integer,inspectionMissionId integer,detailUniqueTag varchar,locationId integer,recordType smallint);";

- (BOOL)createInspectionEquipmentRecordTable
{
    return [[self fmdb] executeUpdate:KSqlCreateInspectionEquipmentRecordTable];
}

- (void)dropAndReCreateInspectionEquipmentRecordTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists inspectionEquipment_record;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreateInspectionEquipmentRecordTable];
        }
    }];
}

#pragma mark - 增删改查等
- (BOOL)saveInspectionMissionWithArray:(NSArray*)inspectionMission
{
    if (inspectionMission.count==0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMissions = inspectionMission;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMissions enumerateObjectsUsingBlock:^(YWInspectionMissionModel* _Nonnull inspectionMission, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT  * FROM 'inspectionMission' WHERE inspectionMissionId=?";
            FMResultSet* set = [db executeQuery:sql, @(inspectionMission.inspectionMissionId)];
            if ([set next]) {
                if (inspectionMission.status != -2) {
                    sql = @"UPDATE 'inspectionMission' SET inspectionLineId=?, inspectionMissionName=?,createTime=?,status=?,creatorId=?,startDate=?,endDate=?,startTime=?,endTime=?,missionSequence=?,buildingId=?,observerIds=? WHERE inspectionMissionId = ?;";
                    executeResult = [db executeUpdate:sql, @(inspectionMission.inspectionLine.inspectionLineId),inspectionMission.inspectionMissionName, @(inspectionMission.createTime), @(inspectionMission.status), @(inspectionMission.creator.uniqueId), @(inspectionMission.startDate), @(inspectionMission.endDate),inspectionMission.startTime,inspectionMission.endTime,inspectionMission.missionSequence, inspectionMission.buildingId,inspectionMission.observerIds,@(inspectionMission.inspectionMissionId)];
                }
            }
            else {
                sql = @"INSERT INTO 'inspectionMission' (inspectionMissionId,inspectionLineId,inspectionMissionName,createTime,status,creatorId,startDate,endDate,startTime,endTime,missionSequence,buildingId,observerIds) VALUES (?,?, ?, ?, ?, ?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(inspectionMission.inspectionMissionId), @(inspectionMission.inspectionLine.inspectionLineId),inspectionMission.inspectionMissionName, @(inspectionMission.createTime), @(inspectionMission.status), @(inspectionMission.creator.uniqueId), @(inspectionMission.startDate), @(inspectionMission.endDate),inspectionMission.startTime,inspectionMission.endTime,inspectionMission.missionSequence,inspectionMission.buildingId,inspectionMission.observerIds];
            }
            //sql = @"DELETE FROM 'reply' WHERE relationId=?";
            //[db executeUpdate:sql, @(inspectionMission.inspectionMissionId)];
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
//    __block NSMutableArray* replysArray = [NSMutableArray array];
    [blockMissions enumerateObjectsUsingBlock:^(YWInspectionMissionModel* _Nonnull inspectionMission, NSUInteger idx, BOOL* _Nonnull stop) {
//        //评论
//        [inspectionMission.replies enumerateObjectsUsingBlock:^(YWReply*  _Nonnull reply, NSUInteger idx, BOOL * _Nonnull stop) {
//            reply.relationId = inspectionMission.inspectionMissionId;
//            reply.relationType = YWReplyRelationTypeInspection;
//        }];
//        [replysArray addObjectsFromArray:inspectionMission.replies];
        //执行人
        [self updateInspectionMissionExecutor:inspectionMission.executors inspectionMissionId:inspectionMission.inspectionMissionId];
        //线路
        [self updateInspectionLineIdAndName:inspectionMission.inspectionLine];
        
        //保存最新一条的线路记录
//        if (inspectionMission.latestRecord) {
//            [self updateInspectionLineRecordWithNewWorkData:@[ inspectionMission.latestRecord ]];
//            //保存巡查记录明细
//            __block NSMutableArray* totalEquipmentRecordArray = [NSMutableArray array];
//            __block NSMutableArray* picturesArray = [NSMutableArray array];
//            __block YWInspectionEquipmentRecord* equRecord = nil;
//            
//            [inspectionMission.latestRecord.details enumerateObjectsUsingBlock:^(YWInspectionEquipmentRecord* _Nonnull detailPointRecord, NSUInteger idx, BOOL* _Nonnull stop) {
//                equRecord = [[YWInspectionEquipmentRecord alloc] init];
//                equRecord.inspectionLineId = inspectionMission.latestRecord.inspectionLineId;
//                equRecord.inspectionMissionId = inspectionMission.latestRecord.inspectionMissionId;
//                equRecord.uniqueTag = inspectionMission.latestRecord.uniqueTag;
//                
//                equRecord.executeTime = detailPointRecord.executeTime;
//                equRecord.executor = detailPointRecord.executor;
//                equRecord.note = detailPointRecord.note;
//                equRecord.equipment = detailPointRecord.equipment;
//                equRecord.recordType = (inspectionMission.inspectionLine.lineType == YWInspectionLineTypeEquipment)?YWInspectionEquipmentRecordTypeEquipment:YWInspectionEquipmentRecordTypeLocation;
//                equRecord.locationId = detailPointRecord.locationId;
//                if (equRecord.equipment.equipmentId.length>0) {
//                    equRecord.equipmentRecordId = [NSString stringWithFormat:@"%@_%@", equRecord.uniqueTag, equRecord.equipment.equipmentId];
//                }else{
//                    equRecord.equipmentRecordId = [NSString stringWithFormat:@"%@_%ld", equRecord.uniqueTag,(long)equRecord.locationId];
//                }
//                
//                
//                equRecord.equipmentFields = detailPointRecord.equipmentFields;
//                equRecord.status = detailPointRecord.status;
//                equRecord.uploadStatus = YWPatrolPointRecordUploadStatusFinished;
//                [detailPointRecord.pictures enumerateObjectsUsingBlock:^(YWPatrolPicture* _Nonnull patrolPicture, NSUInteger idx, BOOL* _Nonnull stop) {
//                    patrolPicture.recordId = equRecord.equipmentRecordId;
//                    patrolPicture.pictureId = [YWCommonTool randStringId];
//                    patrolPicture.pictureName = @"";
//                    patrolPicture.isLocalImage = NO;
//                    patrolPicture.uploadStatus = YWPictureRecordUploadStatusSuccess;
//                    patrolPicture.recordType = YWPictureRecordTypeInspectionMission;
//                    [picturesArray addObject:patrolPicture];
//                }];
//                [totalEquipmentRecordArray addObject:equRecord];
//            }];
//            [self updateInspectionEquipmentRecordWithArray:totalEquipmentRecordArray];
//            [[YWDBTool shareDBTool] updatePatrolPictureWithNewWorkData:picturesArray];
//            if (totalEquipmentRecordArray.count) {
//                NSMutableArray* fieldRecorsArray = [NSMutableArray array];
//                [totalEquipmentRecordArray enumerateObjectsUsingBlock:^(YWInspectionEquipmentRecord* _Nonnull equRecord, NSUInteger idx, BOOL* _Nonnull stop) {
//                    [equRecord.equipmentFields enumerateObjectsUsingBlock:^(YWEquipmentFieldRecord* _Nonnull fieldRecord, NSUInteger idx, BOOL* _Nonnull stop) {
//                        fieldRecord.uniqueTag = equRecord.uniqueTag;
//                        fieldRecord.maintenanceTypeId = YWMaintenanceTypePatrol;
//                        [fieldRecorsArray addObject:fieldRecord];
//                    }];
//                }];
//                [[YWDBTool shareDBTool] updateEquipmentFieldRecords:fieldRecorsArray];
//            }
//        }
    }];
//    //更新评论
//    [[YWReplyDBManager shareManager] updateReplyWithReplyArray:replysArray];
//    [[NSNotificationCenter defaultCenter] postNotificationName:KRefreshInspectionLatestRecordNotification object:nil];
    return executeResult;
}
- (NSString *)allInspectionMissionIds{
    NSMutableString *inspectionMissionIds = [NSMutableString string];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT inspectionMissionId FROM 'inspectionMission' WHERE status = 0";
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            NSInteger inspectionMissionId = [set intForColumn:@"inspectionMissionId"];
            if (inspectionMissionId>0) {
                [inspectionMissionIds appendString:[@(inspectionMissionId) stringValue]];
                [inspectionMissionIds appendString:@","];
            }
        }
        [db closeOpenResultSets];
    }];
    return [NSString stringWithFormat:@"%@",inspectionMissionIds];
}
- (BOOL)updateInspectionMissionDeleteStatusWithMissionId:(NSInteger)inspectionMissionId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'inspectionMission' SET status=? WHERE inspectionMissionId=?;";
        executeResult = [db executeUpdate:sql, @(-1),@(inspectionMissionId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateInspectionMissionExecutor:(NSArray*)executors inspectionMissionId:(NSInteger)inspectionMissionId
{
    if (executors.count==0 || inspectionMissionId<=0) {
        return YES;
    }
    __block NSArray* blockExecutors = executors;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"delete FROM 'inspectionMission_executor_rel' WHERE inspectionMissionId=?;";
        executeResult = [db executeUpdate:sql, @(inspectionMissionId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [blockExecutors enumerateObjectsUsingBlock:^(YWHandler* _Nonnull handler, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'inspectionMission_executor_rel' (inspectionMissionId,uniqueId) VALUES (?, ?);";
            BOOL result = [db executeUpdate:sql, @(inspectionMissionId), @(handler.uniqueId)];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    //保存巡更人到handler
    [[YWDBTool shareDBTool] updateHandlerWithArray:blockExecutors];
    return executeResult;
}

- (BOOL)updateInspectionLine:(YWInspectionLineModel*)inspectionLine
{
    if (inspectionLine.inspectionLineId<=0 || inspectionLine.intervalMinute<=0) {
        return YES;
    }
    __block YWInspectionLineModel* blockInspectionLine = inspectionLine;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"SELECT  * FROM 'inspectionLine' WHERE inspectionLineId=?";
        FMResultSet* set = [db executeQuery:sql,@(blockInspectionLine.inspectionLineId)];
        if ([set next]) {
            sql = @"UPDATE 'inspectionLine' SET inspectionLineName=?,lineType=?,intervalTime=?,creatorId=?,createTime=?,updateTime=?,status=?,note=? WHERE inspectionLineId = ?;";
            executeResult = [db executeUpdate:sql, blockInspectionLine.inspectionLineName,@(blockInspectionLine.lineType), @(blockInspectionLine.intervalMinute), @(blockInspectionLine.creator.uniqueId), @(blockInspectionLine.createTime),@(blockInspectionLine.updateTime), @(blockInspectionLine.status), blockInspectionLine.note, @(blockInspectionLine.inspectionLineId)];
        }
        else {
            sql = @"INSERT INTO 'inspectionLine' (inspectionLineId,inspectionLineName,lineType,intervalTime,creatorId,createTime,updateTime,status,note) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
            executeResult = [db executeUpdate:sql, @(blockInspectionLine.inspectionLineId), blockInspectionLine.inspectionLineName,@(blockInspectionLine.lineType), @(blockInspectionLine.intervalMinute), @(blockInspectionLine.creator.uniqueId), @(blockInspectionLine.createTime),@(blockInspectionLine.updateTime), @(blockInspectionLine.status), blockInspectionLine.note];
        }
        
        if (!executeResult) {
            *rollback = YES;
        }
//        __block NSString* sql = nil;
//        sql = @"delete FROM 'inspectionLine' WHERE inspectionMissionId=?;";
        [db closeOpenResultSets];
    }];
    if (inspectionLine.equipments.count) {
        //更新线路的设备
        [self updateInspectionEquipments:inspectionLine.equipments inspectionLineId:inspectionLine.inspectionLineId];
    }
    if (inspectionLine.locationRels.count) {
        //更新巡查位置
        [self updateInspectionLineLocation:inspectionLine.locationRels inspectionLineId:inspectionLine.inspectionLineId];
    }
    if (inspectionLine.weekRels.count) {
        //巡查星期
        [self updateInspectionLineWeek:inspectionLine.weekRels inspectionLineId:inspectionLine.inspectionLineId];
    }
    return executeResult;
}

- (BOOL)updateInspectionLineWithArray:(NSArray*)inspectionLineArray
{
    if (inspectionLineArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [inspectionLineArray enumerateObjectsUsingBlock:^(YWInspectionLineModel*  _Nonnull blockInspectionLine, NSUInteger idx, BOOL * _Nonnull stop) {
            __block NSString* sql = nil;
            sql = @"SELECT  * FROM 'inspectionLine' WHERE inspectionLineId=?";
            FMResultSet* set = [db executeQuery:sql,@(blockInspectionLine.inspectionLineId)];
            if ([set next]) {
                sql = @"UPDATE 'inspectionLine' SET inspectionLineName=?,lineType=?,intervalTime=?,creatorId=?,createTime=?,updateTime=?,status=?,note=? WHERE inspectionLineId = ?;";
                executeResult = [db executeUpdate:sql, blockInspectionLine.inspectionLineName,@(blockInspectionLine.lineType), @(blockInspectionLine.intervalMinute), @(blockInspectionLine.creator.uniqueId), @(blockInspectionLine.createTime),@(blockInspectionLine.updateTime), @(blockInspectionLine.status), blockInspectionLine.note, @(blockInspectionLine.inspectionLineId)];
            }
            else {
                sql = @"INSERT INTO 'inspectionLine' (inspectionLineId,inspectionLineName,lineType,intervalTime,creatorId,createTime,updateTime,status,note) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql, @(blockInspectionLine.inspectionLineId), blockInspectionLine.inspectionLineName,@(blockInspectionLine.lineType), @(blockInspectionLine.intervalMinute), @(blockInspectionLine.creator.uniqueId), @(blockInspectionLine.createTime),@(blockInspectionLine.updateTime), @(blockInspectionLine.status), blockInspectionLine.note];
            }
            
            if (!executeResult) {
                *rollback = YES;
            }
            //        __block NSString* sql = nil;
            //        sql = @"delete FROM 'inspectionLine' WHERE inspectionMissionId=?;";
        }];
        [db closeOpenResultSets];
    }];
    [inspectionLineArray enumerateObjectsUsingBlock:^(YWInspectionLineModel*  _Nonnull inspectionLine, NSUInteger idx, BOOL * _Nonnull stop) {
        if (inspectionLine.equipments.count) {
            //更新线路的设备
            [self updateInspectionEquipments:inspectionLine.equipments inspectionLineId:inspectionLine.inspectionLineId];
        }
        if (inspectionLine.locationRels.count) {
            //更新巡查位置
            [self updateInspectionLineLocation:inspectionLine.locationRels inspectionLineId:inspectionLine.inspectionLineId];
        }
        if (inspectionLine.weekRels.count) {
            //巡查星期
            [self updateInspectionLineWeek:inspectionLine.weekRels inspectionLineId:inspectionLine.inspectionLineId];
        }
    }];
    
    return executeResult;
}

- (BOOL)updateInspectionLineWeek:(NSArray*)inspectionLineWeekRels inspectionLineId:(NSInteger)inspectionLineId
{
    if (inspectionLineWeekRels.count==0 || inspectionLineId<=0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"delete FROM 'inspectionLine_week_rel' WHERE inspectionLineId=?;";
        executeResult = [db executeUpdate:sql, @(inspectionLineId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [inspectionLineWeekRels enumerateObjectsUsingBlock:^(YWInspectionLineWeekRel* _Nonnull inspectionLineWeekRel, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'inspectionLine_week_rel' (inspectionLineId ,week) VALUES (?, ?);";
            BOOL result = [db executeUpdate:sql, @(inspectionLineId), @(inspectionLineWeekRel.week)];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateInspectionLineLocation:(NSArray*)inspectionLineLocationRels inspectionLineId:(NSInteger)inspectionLineId
{
    if (inspectionLineLocationRels.count==0 || inspectionLineId<=0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        sql = @"delete FROM 'inspectionLine_location_rel' WHERE inspectionLineId=?;";
        executeResult = [db executeUpdate:sql, @(inspectionLineId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [inspectionLineLocationRels enumerateObjectsUsingBlock:^(YWInspectionLineLocationRel* _Nonnull lineLocationRel, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'inspectionLine_location_rel' (inspectionLineId ,sort,locationId,note) VALUES (?, ?, ?, ?);";
            BOOL result = [db executeUpdate:sql, @(inspectionLineId), @(lineLocationRel.sort), @(lineLocationRel.locationId), lineLocationRel.note];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateInspectionEquipments:(NSArray*)equipments inspectionLineId:(NSInteger)inspectionLineId
{
    if (equipments.count==0 || inspectionLineId<=0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"delete FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId=?;";
        executeResult = [db executeUpdate:sql, @(inspectionLineId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [equipments enumerateObjectsUsingBlock:^(YWEquipment* _Nonnull equipment, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'inspectionLine_equipment_rel' (inspectionLineId,equipmentId) VALUES (?, ?);";
            BOOL result = [db executeUpdate:sql, @(inspectionLineId), equipment.equipmentId];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray*)allInspectionMissions
{
    __block NSMutableArray* missions = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        YWLoginUser *currentUser = [YWLoginTool loginUser];
        NSTimeInterval currentTimeInterval = [[NSDate date] timeIntervalSince1970]*1000;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'inspectionMission' WHERE (creatorId=%ld or (inspectionMissionId in (SELECT inspectionMissionId FROM 'inspectionMission_executor_rel' WHERE uniqueId = %ld)) or (observerIds like '%%%ld%%')) AND status = 0 AND endDate>%f ORDER BY createTime DESC",(long)currentUser.uniqueId,(long)currentUser.uniqueId,(long)currentUser.uniqueId,currentTimeInterval];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWInspectionMissionModel* inspectionMission = [self inspectionMissionWithFMResultSet:set FMDatabase:db];
            [missions addObject:inspectionMission];
        }
        [db closeOpenResultSets];
    }];
    return missions;
}

- (NSArray *)inspectionMissionsWithPage:(NSInteger)page{
    __block NSMutableArray* missions = [NSMutableArray array];
    __block NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        YWLoginUser *currentUser = [YWLoginTool loginUser];
        NSTimeInterval currentTimeInterval = [[NSDate date] timeIntervalSince1970]*1000;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'inspectionMission' WHERE (creatorId=%ld or (inspectionMissionId in (SELECT inspectionMissionId FROM 'inspectionMission_executor_rel' WHERE uniqueId = %ld)) or (observerIds like '%%%ld%%')) AND status = 0 AND endDate>%f ORDER BY createTime DESC LIMIT %ld",(long)currentUser.uniqueId,(long)currentUser.uniqueId,(long)currentUser.uniqueId,currentTimeInterval,(long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWInspectionMissionModel *inspectionMission = [self inspectionMissionWithFMResultSet:set FMDatabase:db];
            [missions addObject:inspectionMission];
            //当前巡查区段
            inspectionMission.currentLooperTimeArray = [YWCommonTool looperTimeArrayWithMission:inspectionMission];
            //当前区段的uniqueTag
            inspectionMission.uniqueTag = [YWCommonTool uniqueTagWithMission:inspectionMission];
        }
        [db closeOpenResultSets];
    }];
    return missions;
}

- (NSArray *)myInspectionMissions{
    __block NSMutableArray* missions = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        YWLoginUser *currentUser = [YWLoginTool loginUser];
        NSTimeInterval currentTimeInterval = [[NSDate date] timeIntervalSince1970]*1000;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'inspectionMission' WHERE (creatorId=%ld or (inspectionMissionId in (SELECT inspectionMissionId FROM 'inspectionMission_executor_rel' WHERE uniqueId = %ld))) AND status = 0 AND endDate>%f ORDER BY createTime DESC",(long)currentUser.uniqueId,(long)currentUser.uniqueId,currentTimeInterval];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWInspectionMissionModel* inspectionMission = [self inspectionMissionWithFMResultSet:set FMDatabase:db];
            [missions addObject:inspectionMission];
        }
        [db closeOpenResultSets];
    }];
    return missions;
}
- (NSArray *)allInspectionLines{
    __block NSMutableArray* lines = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionLine' WHERE inspectionLineId in (SELECT distinct inspectionLineId FROM 'inspectionLine' WHERE status = 0) ";
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWInspectionLineModel *inspectionLine = [self inspectionLineWithFMResultSet:set FMDatabase:db];
            [lines addObject:inspectionLine];
        }
        [db closeOpenResultSets];
    }];
    return lines;
}
- (YWInspectionLineModel*)inspectionLineWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWInspectionLineModel *inspectionLine = [[YWInspectionLineModel alloc] init];
    inspectionLine.inspectionLineId = [set intForColumn:@"inspectionLineId"];
    inspectionLine.inspectionLineName = [set stringForColumn:@"inspectionLineName"];
    inspectionLine.lineType = [set intForColumn:@"lineType"];
    inspectionLine.intervalMinute = [set intForColumn:@"intervalTime"];
    inspectionLine.createTime = [set longLongIntForColumn:@"createTime"];
    inspectionLine.updateTime = [set longLongIntForColumn:@"updateTime"];
    inspectionLine.note = [set stringForColumn:@"note"];
    inspectionLine.status = [set intForColumn:@"status"];
    NSInteger creatorId = [set intForColumn:@"creatorId"];
    
    NSString* sql = @"SELECT * FROM 'handler' WHERE uniqueId = ?;";
    FMResultSet* newSet = [db executeQuery:sql, @(creatorId)];
    if ([newSet next]) {
        inspectionLine.creator = [[YWDBTool shareDBTool] handlerWithSet:newSet];
    }
    
    NSMutableArray* equipmentArray = [NSMutableArray array];
    NSString* countSql = @"SELECT * FROM 'equipment' WHERE equipmentId IN (SELECT equipmentId FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId =?) ORDER BY apartmentId,floorId,locationId,equipmentId;";
    newSet = [db executeQuery:countSql, @(inspectionLine.inspectionLineId)];
    while ([newSet next]) {
        YWEquipment* equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:newSet];
        [equipmentArray addObject:equipment];
    }
    inspectionLine.equipments = equipmentArray;
    
    NSMutableArray* locationRels = [NSMutableArray array];
    sql = @"SELECT * FROM 'inspectionLine_location_rel' WHERE inspectionLineId = ?;";
    newSet = [db executeQuery:sql, @(inspectionLine.inspectionLineId)];
    while ([newSet next]) {
        YWInspectionLineLocationRel* locationRel = [[YWInspectionLineLocationRel alloc] init];
        locationRel.inspectionLineId = [newSet intForColumn:@"inspectionLineId"];
        locationRel.sort = [newSet intForColumn:@"sort"];
        locationRel.locationId = [newSet intForColumn:@"locationId"];
        locationRel.note = [newSet stringForColumn:@"note"];
        [locationRels addObject:locationRel];
    }
    inspectionLine.locationRels = locationRels;
    
    NSMutableArray* weekRels = [NSMutableArray array];
    sql = @"SELECT * FROM 'inspectionLine_week_rel' WHERE inspectionLineId = ? ORDER BY week;";
    newSet = [db executeQuery:sql, @(inspectionLine.inspectionLineId)];
    while ([newSet next]) {
        YWInspectionLineWeekRel* weekRel = [[YWInspectionLineWeekRel alloc] init];
        weekRel.inspectionLineId = [newSet intForColumn:@"inspectionLineId"];
        weekRel.week = [newSet intForColumn:@"week"];
        [weekRels addObject:weekRel];
    }
    inspectionLine.weekRels = weekRels;
    
    return inspectionLine;
}

- (YWInspectionMissionModel *)inspectionMissionWithMissionId:(NSInteger)missionId{
    __block YWInspectionMissionModel* inspectionMission = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionMission' WHERE inspectionMissionId=?";
        FMResultSet* set = [db executeQuery:sql,@(missionId)];
        if ([set next]) {
            inspectionMission = [self inspectionMissionWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return inspectionMission;
}

- (YWInspectionMissionModel*)inspectionMissionWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWInspectionMissionModel* mission = [[YWInspectionMissionModel alloc] init];
    mission.inspectionMissionId = [set intForColumn:@"inspectionMissionId"];
    mission.inspectionMissionName = [set stringForColumn:@"inspectionMissionName"];
    mission.buildingId = [set stringForColumn:@"buildingId"];
    mission.createTime = [set longLongIntForColumn:@"createTime"];
    mission.status = [set intForColumn:@"status"];
    mission.startDate = [set longLongIntForColumn:@"startDate"];
    mission.endDate = [set longLongIntForColumn:@"endDate"];
    mission.startTime= [set stringForColumn:@"startTime"];
    mission.endTime = [set stringForColumn:@"endTime"];
    mission.missionSequence = [set stringForColumn:@"missionSequence"];
    
    mission.observerIds = [set  stringForColumn:@"observerIds"];
    NSMutableArray *observer = [NSMutableArray array];
    NSString *observerSql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",mission.observerIds];
    FMResultSet *observerSet = [db executeQuery:observerSql];
    while ([observerSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:observerSet];
        [observer addObject:handler];
    }
    mission.observers = observer;
    
    NSInteger missionCreatorId = [set intForColumn:@"creatorId"];
    NSString* creatorsql = @"SELECT * FROM 'handler' WHERE uniqueId = ?;";
    FMResultSet* newSet = [db executeQuery:creatorsql, @(missionCreatorId)];
    if ([newSet next]) {
        mission.creator = [[YWDBTool shareDBTool] handlerWithSet:newSet];
    }
    NSInteger inspectionLineId = [set intForColumn:@"inspectionLineId"];
    NSString* sql = @"SELECT * FROM 'inspectionLine' WHERE inspectionLineId = ?;";
    newSet = [db executeQuery:sql, @(inspectionLineId)];
    YWInspectionLineModel* inspectionLine = [[YWInspectionLineModel alloc] init];
    while ([newSet next]) {
        inspectionLine = [self inspectionLineWithFMResultSet:newSet FMDatabase:db];
    }
    mission.inspectionLine = inspectionLine;
    
    NSMutableArray* executorIdArray = [NSMutableArray array];
    NSString* executorsql = @"SELECT * FROM 'inspectionMission_executor_rel' WHERE inspectionMissionId = ?;";
    newSet = [db executeQuery:executorsql, @(mission.inspectionMissionId)];
    while ([newSet next]) {
        NSInteger executorID = [newSet intForColumn:@"uniqueId"];
        [executorIdArray addObject:[NSString stringWithFormat:@"%ld", (long)executorID]];
    }
    NSMutableArray* executorArray = [NSMutableArray array];
    [executorIdArray enumerateObjectsUsingBlock:^(NSString* _Nonnull obj, NSUInteger idx, BOOL* _Nonnull stop) {
        NSInteger uniqueId = [obj integerValue];
        NSString* sql = @"SELECT * FROM 'handler' WHERE uniqueId=?;";
        FMResultSet* set = [db executeQuery:sql, @(uniqueId)];
        while ([set next]) {
            YWHandler* handler = [[YWDBTool shareDBTool] handlerWithSet:set];
            [executorArray addObject:handler];
        }
    }];
    mission.executors = executorArray;
    return mission;
}

////该方法是本地巡更时更新数据用，下面是从网络获取数据更新数据库方法
- (BOOL)updateInspectionLineRecordWithArray:(NSArray*)inspectionLineRecords
{
    if (inspectionLineRecords.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [inspectionLineRecords enumerateObjectsUsingBlock:^(YWInspectionLineRecord* _Nonnull lineRecord, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT  * FROM 'inspectionline_record' WHERE inspectionLineId=? AND uniqueTag = ?";
            FMResultSet* set = [db executeQuery:sql, @(lineRecord.inspectionLineId), lineRecord.uniqueTag];
            
            if ([set next]) {
                sql = @"UPDATE 'inspectionline_record' SET startTime=?,endTime=?,status=?,count=?,result=?,executorIds=?,inspectionMissionId=?,recordType=?,recordSequence=? WHERE inspectionLineId = ? AND uniqueTag = ?;";
                executeResult = [db executeUpdate:sql, @(lineRecord.startTime), @(lineRecord.endTime), @(0), @(lineRecord.count), @(lineRecord.result), lineRecord.executorIds, @(lineRecord.inspectionMissionId),@(lineRecord.recordType),lineRecord.recordSequence, @(lineRecord.inspectionLineId), lineRecord.uniqueTag];
            }
            else {
                sql = @"INSERT INTO 'inspectionline_record' (inspectionLineId, uniqueTag,startTime,endTime,status,count,result,executorIds,inspectionMissionId,recordType,recordSequence) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(lineRecord.inspectionLineId), lineRecord.uniqueTag, @(lineRecord.startTime), @(lineRecord.endTime), @(0), @(lineRecord.count), @(lineRecord.result), lineRecord.executorIds, @(lineRecord.inspectionMissionId),@(lineRecord.recordType),lineRecord.recordSequence];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    
    return executeResult;
}
- (BOOL)updateInspectionLineRecordWithNewWorkData:(NSArray*)inspectionLineRecords
{
    if (inspectionLineRecords.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [inspectionLineRecords enumerateObjectsUsingBlock:^(YWInspectionLineRecord* _Nonnull lineRecord, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT  * FROM 'inspectionline_record' WHERE inspectionLineId=? AND uniqueTag = ?";
            FMResultSet* set = [db executeQuery:sql, @(lineRecord.inspectionLineId), lineRecord.uniqueTag];
            ////网络数据过来的有些字段没有数据就不更新，因为会覆盖掉本地记录，例如count
            if ([set next]) {
                sql = @"UPDATE 'inspectionline_record' SET startTime=?,endTime=?,status=?,result=?,executorIds=?,inspectionMissionId=?,recordType=?,recordSequence=? WHERE inspectionLineId = ? AND uniqueTag = ?;";
                executeResult = [db executeUpdate:sql, @(lineRecord.startTime), @(lineRecord.endTime), @(0), @(lineRecord.result), lineRecord.executorIds, @(lineRecord.inspectionMissionId),@(lineRecord.recordType),lineRecord.recordSequence, @(lineRecord.inspectionLineId), lineRecord.uniqueTag];
            }
            else {
                ////过来的数据
                sql = @"INSERT INTO 'inspectionline_record' (inspectionLineId, uniqueTag,startTime,endTime,status,count,result,executorIds,inspectionMissionId,recordType,recordSequence) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(lineRecord.inspectionLineId), lineRecord.uniqueTag, @(lineRecord.startTime), @(lineRecord.endTime), @(0), @(lineRecord.count), @(lineRecord.result), lineRecord.executorIds, @(lineRecord.inspectionMissionId),@(lineRecord.recordType),lineRecord.recordSequence];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    [inspectionLineRecords enumerateObjectsUsingBlock:^(YWInspectionLineRecord* _Nonnull lineRecord, NSUInteger idx, BOOL* _Nonnull stop) {
        if (lineRecord.inspectionLine.inspectionLineId) {
            [self updateInspectionLineIdAndName:lineRecord.inspectionLine];
        }
    }];
    return executeResult;
}

- (BOOL)updateInspectionEquipmentRecordWithArray:(NSArray*)inspectionEquipmentRecords
{
    if (inspectionEquipmentRecords.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [inspectionEquipmentRecords enumerateObjectsUsingBlock:^(YWInspectionEquipmentRecord* _Nonnull record, NSUInteger idx, BOOL* _Nonnull stop) {
            NSString * sql = nil;
            FMResultSet * set = nil;
            if (record.recordType == YWInspectionEquipmentRecordTypeEquipment) {
                sql = @"SELECT  * FROM 'inspectionEquipment_record' WHERE uniqueTag=? AND equipmentId = ? ";
                set = [db executeQuery:sql, record.uniqueTag, record.equipment.equipmentId];
            }else{
                sql = @"SELECT  * FROM 'inspectionEquipment_record' WHERE uniqueTag=? AND locationId = ? ";
                set = [db executeQuery:sql, record.uniqueTag, @(record.locationId)];
            }
            if ([set next]) {
                NSTimeInterval executeTime = [set longLongIntForColumn:@"finishTime"];
                //可能会做重复的点，保留最新的那一条
                if (record.executeTime > executeTime || record.status==1) {
                    if (record.recordType == YWInspectionEquipmentRecordTypeEquipment) {
                        sql = @"UPDATE 'inspectionEquipment_record' SET inspectionEquipmentRecordId=?,status =?, finishTime=?,recordNote=?,uploadStatus=?,executorId=?,inspectionLineId=? ,inspectionMissionId=?,detailUniqueTag=?,locationId=?,recordType=? WHERE uniqueTag =? AND equipmentId =? ;";
                        executeResult = [db executeUpdate:sql, record.equipmentRecordId, @(record.status), @(record.executeTime), record.note, @(record.uploadStatus), @(record.executor.uniqueId), @(record.inspectionLineId), @(record.inspectionMissionId),record.detailUniqueTag,@(record.locationId),@(record.recordType), record.uniqueTag,record.equipment.equipmentId];
                    }else{
                        sql = @"UPDATE 'inspectionEquipment_record' SET inspectionEquipmentRecordId=?,status =?, finishTime=?,recordNote=?,uploadStatus=?,executorId=?,inspectionLineId=? ,inspectionMissionId=?,detailUniqueTag=?,equipmentId=?,recordType=? WHERE uniqueTag =? AND locationId=? ;";
                        executeResult = [db executeUpdate:sql, record.equipmentRecordId, @(record.status), @(record.executeTime), record.note, @(record.uploadStatus), @(record.executor.uniqueId), @(record.inspectionLineId), @(record.inspectionMissionId),record.detailUniqueTag,record.equipment.equipmentId,@(record.recordType), record.uniqueTag, @(record.locationId)];
                    }
                    
                }
            }
            else {
                sql = @"INSERT INTO 'inspectionEquipment_record' (inspectionEquipmentRecordId,uniqueTag,status,equipmentId,finishTime,recordNote,uploadStatus,executorId,inspectionLineId ,inspectionMissionId,detailUniqueTag,locationId,recordType) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql, record.equipmentRecordId, record.uniqueTag, @(record.status), record.equipment.equipmentId, @(record.executeTime), record.note, @(record.uploadStatus), @(record.executor.uniqueId), @(record.inspectionLineId), @(record.inspectionMissionId),record.detailUniqueTag,@(record.locationId),@(record.recordType)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateLocationInspectionRecordWithArray:(NSArray*)locationInspectionRecords{
    if (locationInspectionRecords.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [locationInspectionRecords enumerateObjectsUsingBlock:^(YWInspectionEquipmentRecord* _Nonnull record, NSUInteger idx, BOOL* _Nonnull stop) {
            FMResultSet* set = nil;
            sql = @"SELECT  * FROM 'inspectionEquipment_record' WHERE uniqueTag=? AND locationId = ? ";
            set = [db executeQuery:sql, record.uniqueTag, @(record.locationId)];
            if ([set next]) {
                NSTimeInterval executeTime = [set longLongIntForColumn:@"finishTime"];
                //可能会做重复的点，保留最新的那一条
                if (record.executeTime > executeTime || record.status==1) {
                    //不是全字段更新(因为过来的数据不完全,线路id，任务id没有)
                    sql = @"UPDATE 'inspectionEquipment_record' SET inspectionEquipmentRecordId=?,status =?, finishTime=?,recordNote=?,uploadStatus=?,executorId=?,detailUniqueTag=?,recordType=? WHERE uniqueTag =? AND locationId=? ;";
                    executeResult = [db executeUpdate:sql, record.equipmentRecordId, @(record.status), @(record.executeTime), record.note, @(record.uploadStatus), @(record.executor.uniqueId),record.detailUniqueTag,@(record.recordType), record.uniqueTag, @(record.locationId)];
                }
            }
            else {
                sql = @"INSERT INTO 'inspectionEquipment_record' (inspectionEquipmentRecordId,uniqueTag,status,equipmentId,finishTime,recordNote,uploadStatus,executorId,inspectionLineId ,inspectionMissionId,detailUniqueTag,locationId,recordType) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql, record.equipmentRecordId, record.uniqueTag, @(record.status), record.equipment.equipmentId, @(record.executeTime), record.note, @(record.uploadStatus), @(record.executor.uniqueId), @(record.inspectionLineId), @(record.inspectionMissionId),record.detailUniqueTag,@(record.locationId),@(record.recordType)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray*)inspectionEquipmentRecordWithUniqueTag:(NSString*)uniqueTag
{
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionEquipment_record' WHERE uniqueTag=? order by finishTime DESC;";
        FMResultSet* set = [db executeQuery:sql, uniqueTag];
        while ([set next]) {
            YWInspectionEquipmentRecord* record = [self inspectionEquipmentRecordWithFMResultSet:set FMDatabase:db];
            [records addObject:record];
        }
        [db closeOpenResultSets];
    }];
    return records;
}

- (NSInteger)countOfInspectionEquipmentRecordWithUniqueTag:(NSString*)uniqueTag{
    __block NSInteger recordCount = 0;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* countSql = @"SELECT COUNT(*) AS recordCount FROM 'inspectionEquipment_record' WHERE uniqueTag=?";
        FMResultSet* countSet = [db executeQuery:countSql,uniqueTag];
        if ([countSet next]) {
            recordCount = [countSet intForColumn:@"recordCount"];
        }
        [db closeOpenResultSets];
    }];
    return recordCount;
}

- (BOOL)isExistRecordWithUniqueTag:(NSString*)uniqueTag equipmentId:(NSString*)equipmentId
{
    __block BOOL ret = NO;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* selectSQL = @"SELECT * FROM 'inspectionEquipment_record' WHERE uniqueTag=? AND equipmentId=? AND recordType = ?;";
        FMResultSet* set = [db executeQuery:selectSQL, uniqueTag, equipmentId,@(YWInspectionEquipmentRecordTypeEquipment)];
        if ([set next]) {
            ret = YES;
        }
        [db closeOpenResultSets];
    }];
    return ret;
}
- (BOOL)isExistEquipmentWithLineId:(NSInteger)inspectionLineId{
    __block BOOL ret = NO;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* selectSQL = @"SELECT * FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId=? LIMIT 1";
        FMResultSet* set = [db executeQuery:selectSQL, @(inspectionLineId)];
        if ([set next]) {
            ret = YES;
        }
        [db closeOpenResultSets];
    }];
    return ret;
}
- (BOOL)isExistLocationWithLineId:(NSInteger)inspectionLineId{
    __block BOOL ret = NO;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* selectSQL = @"SELECT * FROM 'inspectionLine_location_rel' WHERE inspectionLineId=? LIMIT 1";
        FMResultSet* set = [db executeQuery:selectSQL, @(inspectionLineId)];
        if ([set next]) {
            ret = YES;
        }
        [db closeOpenResultSets];
    }];
    return ret;
}
- (BOOL)updateInspectionEquipmentRecordUploadStatus:(NSInteger)uploadStatus inspectionEquipmentRecordId:(NSString*)recordId
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'inspectionEquipment_record' SET uploadStatus=? WHERE inspectionEquipmentRecordId=?;";
        executeResult = [db executeUpdate:sql, @(uploadStatus), recordId];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (NSArray*)inspectionEquipmentRecordsWithUploadStatusFailWithType:(NSInteger)type
{
    __block NSMutableArray *records = [NSMutableArray array];
    __block NSMutableArray *uniqueTagArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *selectSql = @"SELECT distinct uniqueTag FROM 'inspectionEquipment_record' WHERE (uploadStatus=? or uploadStatus=?) AND recordType=?";
        FMResultSet* resuleSet = [db executeQuery:selectSql, @(YWInspectionEquipmentRecordUploadStatusUnFinished),@(YWInspectionEquipmentRecordUploadStatusUploading),@(type)];
        while ([resuleSet next]) {
            NSString *uniqueTag = [resuleSet stringForColumn:@"uniqueTag"];
            if (![uniqueTagArray containsObject:uniqueTag]) {
                [uniqueTagArray addObject:uniqueTag];
            }
        }
        [uniqueTagArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull uniqueTag, NSUInteger idx, BOOL * _Nonnull stop) {
            NSMutableArray *tempArray = [NSMutableArray array];
            NSString *sql = nil;
            FMResultSet* set = nil;
            if (type == YWInspectionEquipmentRecordTypeLocation) {
                sql = @"SELECT * FROM 'inspectionEquipment_record' WHERE uniqueTag=? AND locationId>0 AND (uploadStatus=? or uploadStatus=?)";
                set = [db executeQuery:sql, uniqueTag,@(YWInspectionEquipmentRecordUploadStatusUnFinished),@(YWInspectionEquipmentRecordUploadStatusUploading)];
            }else{
                sql = @"SELECT * FROM 'inspectionEquipment_record' WHERE uniqueTag=? AND (uploadStatus=? or uploadStatus=?)";
                set = [db executeQuery:sql, uniqueTag,@(YWInspectionEquipmentRecordUploadStatusUnFinished),@(YWInspectionEquipmentRecordUploadStatusUploading)];
            }
            while ([set next]) {
                YWInspectionEquipmentRecord* equipmentRecord = [self inspectionEquipmentRecordWithFMResultSet:set FMDatabase:db];
                [tempArray addObject:equipmentRecord];
                if (tempArray.count == 20) {//限制一次提交的点不超过50个
                    [records addObject:tempArray];
                    tempArray = [NSMutableArray array];
                }
            }
            if (tempArray.count>0 && ![records containsObject:tempArray]) {
                [records addObject:tempArray];
            }
            //限制一次离线取出的数据不超过600条，防止内存太高
            if (records.count == 30) {
                *stop = YES;
            }
        }];
        
        [db closeOpenResultSets];
    }];
    return records;
}

- (NSArray *)uploadFailedInspectionRecords{
    __block NSMutableArray *records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *sql = @"SELECT * FROM 'inspectionEquipment_record' WHERE (uploadStatus=? or uploadStatus=?)";
        FMResultSet* set = [db executeQuery:sql, @(YWInspectionEquipmentRecordUploadStatusUnFinished),@(YWInspectionEquipmentRecordUploadStatusUploading)];
        while ([set next]) {
            YWInspectionEquipmentRecord* equipmentRecord = [self inspectionEquipmentRecordWithFMResultSet:set FMDatabase:db];
            [records addObject:equipmentRecord];
        }
        [db closeOpenResultSets];
    }];
    return records;
}

- (YWInspectionEquipmentRecord*)inspectionEquipmentRecordWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWInspectionEquipmentRecord* equipmentRecord = [[YWInspectionEquipmentRecord alloc] init];
    equipmentRecord.equipmentRecordId = [set stringForColumn:@"inspectionEquipmentRecordId"];
    equipmentRecord.uniqueTag = [set stringForColumn:@"uniqueTag"];
    equipmentRecord.detailUniqueTag = [set stringForColumn:@"detailUniqueTag"];
    equipmentRecord.status = [set intForColumn:@"status"];
    equipmentRecord.locationId = [set intForColumn:@"locationId"];
    equipmentRecord.recordType = [set intForColumn:@"recordType"];
    
    NSString* equipmentId = [set stringForColumn:@"equipmentId"];
    equipmentRecord.equipmentId = equipmentId;
    NSString* equSql = @"SELECT * FROM 'equipment' WHERE equipmentId=?;";
    FMResultSet* newSet = [db executeQuery:equSql, equipmentId];
    if ([newSet next]) {
        YWEquipment* equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:newSet];
        equipmentRecord.equipment = equipment;
    }
    
    equipmentRecord.executeTime = [set longLongIntForColumn:@"finishTime"];
    equipmentRecord.note = [set stringForColumn:@"recordNote"];
    equipmentRecord.uploadStatus = [set intForColumn:@"uploadStatus"];
    NSInteger handlerId = [set intForColumn:@"executorId"];
    NSString* handlersql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* handlerSet = [db executeQuery:handlersql, @(handlerId)];
    if ([handlerSet next]) {
        equipmentRecord.executor = [[YWDBTool shareDBTool] handlerWithSet:handlerSet];
    }
    
    equipmentRecord.inspectionLineId = [set intForColumn:@"inspectionLineId"];
    equipmentRecord.inspectionMissionId = [set intForColumn:@"inspectionMissionId"];
    NSString *dayMonthString = [YWCommonTool showTime:equipmentRecord.executeTime / 1000 andFormatter:KTimeFormatWithOutDayMonth];
    equipmentRecord.dayMonthString = dayMonthString;
    return equipmentRecord;
}

- (YWInspectionLineRecord*)inspectionLineRecordWithUniqueTag:(NSString*)uniqueTag{
    __block YWInspectionLineRecord* lineRecord = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionline_record' WHERE uniqueTag=?";
        FMResultSet* set = [db executeQuery:sql, uniqueTag];
        if ([set next]) {
            lineRecord = [self inspectionLineRecordWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return lineRecord;
}

- (YWInspectionLineRecord*)inspectionLineRecordWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWInspectionLineRecord* lineRecord = [[YWInspectionLineRecord alloc] init];
    lineRecord.inspectionLineId = [set intForColumn:@"inspectionLineId"];
    NSString* sql = @"SELECT * FROM 'inspectionLine' WHERE inspectionLineId = ?;";
    FMResultSet* newSet = [db executeQuery:sql, @(lineRecord.inspectionLineId)];
    if ([newSet next]) {
        lineRecord.inspectionLine = [self inspectionLineWithFMResultSet:newSet FMDatabase:db];
    }
    lineRecord.uniqueTag = [set stringForColumn:@"uniqueTag"];
    lineRecord.startTime = [set longLongIntForColumn:@"startTime"];
    lineRecord.endTime = [set longLongIntForColumn:@"endTime"];
    lineRecord.count = [set intForColumn:@"count"];
    lineRecord.result = [set intForColumn:@"result"];
    lineRecord.recordSequence = [set stringForColumn:@"recordSequence"];
    
    lineRecord.executorIds = [set stringForColumn:@"executorIds"];
    NSMutableArray *executors = [NSMutableArray array];
    sql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",lineRecord.executorIds];
    FMResultSet *executorSet = [db executeQuery:sql];
    while ([executorSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:executorSet];
        [executors addObject:handler];
    }
    lineRecord.executors = executors;
    
    lineRecord.recordType = [set intForColumn:@"recordType"];
    lineRecord.inspectionMissionId = [set intForColumn:@"inspectionMissionId"];
    sql = @"SELECT * FROM 'inspectionMission' WHERE inspectionMissionId=?";
    FMResultSet* missionSet = [db executeQuery:sql,@(lineRecord.inspectionMissionId)];
    if ([missionSet next]) {
        lineRecord.inspectionMission = [self inspectionMissionWithFMResultSet:missionSet FMDatabase:db];
    }
    
    return lineRecord;
}
- (NSArray*)inspectionLineRecordWithInspectionMission:(YWInspectionMissionModel*)inspectionMission page:(NSInteger)page{
    __block NSMutableArray* totalRecords = [NSMutableArray array];
    __block NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionline_record' WHERE inspectionLineId=? AND inspectionMissionId = ? ORDER BY endTime DESC LIMIT ?";
        FMResultSet* set = [db executeQuery:sql, @(inspectionMission.inspectionLine.inspectionLineId), @(inspectionMission.inspectionMissionId), @(showCount)]; //没有status@(YWInspectionLineRecordStatusFinish)
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWInspectionLineRecord* lineRecord = [self inspectionLineRecordWithFMResultSet:set FMDatabase:db];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:lineRecord.endTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:lineRecord];
            if (![totalRecords containsObject:array] && array.count) {
                [totalRecords addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    
    return totalRecords;
}

- (NSArray*)inspectionLineRecordWithPage:(NSInteger)page{
    __block NSInteger showCount = page * 10;
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *sql = @"SELECT * FROM 'inspectionline_record' ORDER BY endTime DESC LIMIT ?";
        FMResultSet* set = [db executeQuery:sql,@(showCount)];
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWInspectionLineRecord* lineRecord = [self inspectionLineRecordWithFMResultSet:set FMDatabase:db];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:lineRecord.endTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:lineRecord];
            if (![records containsObject:array] && array.count) {
                [records addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return records;
}

-(BOOL)updateReadStatusWithInspectionMissionId:(NSInteger)inspectionMissionId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'inspectionline_record' SET isRead=? WHERE inspectionMissionId=?;";
        executeResult = [db executeUpdate:sql, @(YES),@(inspectionMissionId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (YWInspectionLineModel*)inspectionLineWithLineId:(NSInteger)inspectionLineId
{
    __block YWInspectionLineModel* inspectionLine = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionLine' WHERE inspectionLineId=?";
        FMResultSet* set = [db executeQuery:sql, @(inspectionLineId)];
        if ([set next]) {
            inspectionLine = [self inspectionLineWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    
    return inspectionLine;
}

- (NSArray*)allInspectionEquipmentWithLineId:(NSInteger)inspectionLineId
{
    __block NSMutableArray* equipments = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'equipment' WHERE equipmentId IN (SELECT equipmentId FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId=?) ORDER BY apartmentId,floorId,locationId,equipmentId;";
        FMResultSet* set = [db executeQuery:sql, @(inspectionLineId)];
        while ([set next]) {
            YWEquipment* equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:set];
            [equipments addObject:equipment];
        }
        [db closeOpenResultSets];
    }];
    return equipments;
}
- (NSArray*)allInspectionLocationWithInspectionLineId:(NSInteger)inspectionLineId{
    __block NSMutableArray* locations = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'inspectionLine_location_rel' WHERE inspectionLineId=? ORDER BY sort";
        FMResultSet* set = [db executeQuery:sql, @(inspectionLineId)];
        while ([set next]) {
            YWInspectionLineLocationRel* locationRel = [[YWInspectionLineLocationRel alloc] init];
            locationRel.inspectionLineId = [set intForColumn:@"inspectionLineId"];
            locationRel.sort = [set intForColumn:@"sort"];
            locationRel.locationId = [set intForColumn:@"locationId"];
            locationRel.note = [set stringForColumn:@"note"];
            [locations addObject:locationRel];
            
            NSString* locationSql = @"SELECT * FROM 'location' WHERE locationId = ?;";
            FMResultSet* locationSet = [db executeQuery:locationSql, @(locationRel.locationId)];
            if ([locationSet next]) {
                locationRel.location = [[YWDBTool shareDBTool] locationWithFMResultSet:locationSet];
            }
        }
        [db closeOpenResultSets];
    }];
//    for (YWPatrolLineLocationRel* locationRel in locations) {
//        locationRel.location = [[YWDBTool shareDBTool] locationWithId:locationRel.locationId];
//    }
    return locations;
}

- (NSArray*)inspectionLocationNoteWithLocationId:(NSInteger)locationId lineIds:(NSArray*)inspectionLineIdArray{
    __block NSMutableArray* locations = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        [inspectionLineIdArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull inspectionLineId, NSUInteger idx, BOOL * _Nonnull stop) {
            NSInteger lineId = [inspectionLineId integerValue];
            NSString* sql = @"SELECT * FROM 'inspectionLine_location_rel' WHERE locationId=? AND inspectionLineId=? ORDER BY inspectionLineId";
            FMResultSet* set = [db executeQuery:sql, @(locationId),@(lineId)];
            while ([set next]) {
                NSString* note = [set stringForColumn:@"note"];
                if (note.length) {
                    YWInspectionLineLocationRel* locationRel = [[YWInspectionLineLocationRel alloc] init];
                    locationRel.inspectionLineId = [set intForColumn:@"inspectionLineId"];
                    locationRel.sort = [set intForColumn:@"sort"];
                    locationRel.locationId = [set intForColumn:@"locationId"];
                    locationRel.note = note;
                    [locations addObject:locationRel];
                }
            }
        }];
        
        [db closeOpenResultSets];
    }];
    //    for (YWPatrolLineLocationRel* locationRel in locations) {
    //        locationRel.location = [self locationWithId:locationRel.locationId];
    //    }
    return locations;
}

- (NSArray*)allInspectionEquipmentWithLineId:(NSInteger)inspectionLineId qrcodeId:(NSString*)qrcodeId orLocationId:(NSInteger)locationId
{
    __block NSMutableArray* equipments = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        if (qrcodeId) {
            sql = @"SELECT * FROM 'equipment' WHERE qrcodeId=? AND equipmentId IN (SELECT equipmentId FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId=?)";
            set = [db executeQuery:sql, qrcodeId, @(inspectionLineId)];
        }else if (locationId){
            sql = @"SELECT * FROM 'equipment' WHERE locationId=? AND equipmentId IN (SELECT equipmentId FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId=?)";
            set = [db executeQuery:sql, @(locationId), @(inspectionLineId)];
        }
        
        while ([set next]) {
            YWEquipment* equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:set];
            [equipments addObject:equipment];
        }
        [db closeOpenResultSets];
    }];
    return equipments;
}
- (NSArray*)allInspectionEquipmentWithLineId:(NSInteger)inspectionLineId qrcodeId:(NSString*)qrcodeId equipmentTypeId:(NSInteger)equipmentTypeId
{
    __block NSMutableArray* equipments = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'equipment' WHERE qrcodeId=? AND equipmentTypeId=? AND equipmentId IN (SELECT equipmentId FROM 'inspectionLine_equipment_rel' WHERE inspectionLineId=?);";
        FMResultSet* set = [db executeQuery:sql, qrcodeId, @(equipmentTypeId), @(inspectionLineId)];
        while ([set next]) {
            YWEquipment* equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:set];
            [equipments addObject:equipment];
        }
        [db closeOpenResultSets];
    }];
    return equipments;
}

- (NSArray *)needRequestInspectionlLines{
    __block NSMutableArray* lineArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT inspectionLineId,updateTime FROM 'inspectionLine' WHERE inspectionLineId in (select distinct inspectionLineId FROM 'inspectionMission' where inspectionMissionId>0 AND inspectionLineId>0 AND status=0)";
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWInspectionLineModel *inspectionLine = [[YWInspectionLineModel alloc] init];
            inspectionLine.inspectionLineId = [set intForColumn:@"inspectionLineId"];
            inspectionLine.updateTime = [set longLongIntForColumn:@"updateTime"];
            [lineArray addObject:inspectionLine];
        }
        [db closeOpenResultSets];
    }];
    return lineArray;
}

- (BOOL)updateInspectionLineIdAndName:(YWInspectionLineModel *)inspectionLine{
    if (inspectionLine.inspectionLineId<=0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        NSString* sql = @"SELECT  * FROM 'inspectionLine' WHERE inspectionLineId = ?;";
        FMResultSet* set = [db executeQuery:sql,@(inspectionLine.inspectionLineId)];
        if ([set next]) {
            sql = @"UPDATE 'inspectionLine' SET inspectionLineName=? WHERE inspectionLineId = ?";
            executeResult = [db executeUpdate:sql, inspectionLine.inspectionLineName,@(inspectionLine.inspectionLineId)];
        }else {
            sql = @"INSERT INTO 'inspectionLine' (inspectionLineId,inspectionLineName) VALUES (?,?);";
            executeResult = [db executeUpdate:sql, @(inspectionLine.inspectionLineId), inspectionLine.inspectionLineName];
        }
        if (!executeResult) {
            *rollback = YES;
        }
        [db closeOpenResultSets];
    }];
    if (inspectionLine.equipments.count) {
        //更新线路的设备
        [self updateInspectionEquipments:inspectionLine.equipments inspectionLineId:inspectionLine.inspectionLineId];
    }
    if (inspectionLine.locationRels.count) {
        //更新巡查位置
        [self updateInspectionLineLocation:inspectionLine.locationRels inspectionLineId:inspectionLine.inspectionLineId];
    }
    if (inspectionLine.weekRels.count) {
        //巡查星期
        [self updateInspectionLineWeek:inspectionLine.weekRels inspectionLineId:inspectionLine.inspectionLineId];
    }
    return executeResult;
}

@end
